/**

	exec rev_rpt_report 0,'0','1','2006','0','0','0','requested'
select * from rma_table
*/

ALTER          proc rev_rpt_report

@product_Id int,
@customer_Id varchar(20),
@quarter varchar(10),
@year  varchar(10),
@salesRep varchar(50),
@endUser varchar(50),
@defaultPrompt varchar(50),
@category varchar(15)

as

/* variable to store the values */
	
	declare @part_Number varchar(50)
	declare @customer_Name varchar(65)
	declare @schedule_Date datetime
	declare @amount float
	declare @status varchar(50)
	declare @date_From varchar(10)
	declare @date_To varchar(10)
	declare @dateDiff datetime

	declare @month varchar(10)
	declare @sales_rep varchar(50)
	declare @end_user varchar(50)

	declare @LineItem_ID int
	declare @Unit_Price float	
	declare @Shipped_Qty int
	declare @Shipped_Amount float
		
	declare @revised_date datetime 
	declare @requested_date datetime

	declare @req_date datetime

	-- Prabhanjan (24-07-2004) -- Set the default starting month to January. This is temporary fix. Need to get the starting month from Company setup.
	set @month = '1'	

	set @date_From = CONVERT(varchar,CONVERT(datetime,@month+'/01/'+@year),101)
	set @date_To   = CONVERT(varchar,DATEADD(month,12,CONVERT(datetime,@date_From)),101)
	set @dateDiff  = CONVERT(datetime,DATEDIFF(day,1,@date_To),101)
	set @date_To   = CONVERT(varchar,@dateDiff,101)

	declare @results Table(PartNumber varchar(50),CustomerName varchar(65),ScheduleDate datetime, Amount float,Status varchar(15),Type varchar(5), SalesRep varchar(50), EndUser varchar(50))

	declare @CoolCursor CURSOR
	declare @CsolCursor CURSOR

	if (@product_Id <> '0' and @customer_Id <> '0' )
	begin
		
		set @CsolCursor = CURSOR for
		select P.Part_Number, C.C_Name, CST.CST_Ship_Date, (COOL.COOL_Unit_Cost*CSLIT.CSLIT_Qty_Shipped) as Amount, CSLIT_Status, COOL_Requested_Date, SO.SO_Sales_Person, 
              	CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER		
		from Customer_Open_Order_LineItem COOL, 
		     customer_shipping_line_item_table CSLIT,
		     Product P, Sales_Order SO, Customer C , Customer_Shipping_Table CST

		where CST.CST_Ship_Date>=@date_From
		and CST.CST_Ship_Date<=@date_To
		and CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID
		and CSLIT.LineItem_ID = COOL.LineItem_ID
		and COOL.Product_ID = @product_Id
                and COOL.Product_ID = P.Product_ID
		and COOL.SalesOrder_ID = SO.SalesOrder_ID
		and SO.SO_Is_Sample = 1
		and SO.Customer_ID = @customer_Id
                and SO.Customer_ID = C.Customer_ID 
		and CST.CST_STATUS = 'SHIPPED'
		and so_is_consignment = 1			

		if (@category = 'REQUESTED')                
        	begin
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                       	CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER			
			from product, 
			     Customer_Open_Order_LineItem COOL, 
			     Sales_Order,Customer 

			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
	                and customer.Customer_ID= @customer_Id 
			and COOL.Product_ID= @product_Id
			and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
		end
		else if (@category = 'SCHEDULE')
		begin

			
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER,		
                        COOL.COOL_Requested_Date
			from product, 
			     Customer_Open_Order_LineItem COOL, 
			     Sales_Order,Customer 

			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
			and COOL.Product_ID= @product_Id
			and customer.Customer_ID= @customer_Id
			and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	                		
		end
	end
	else if (@product_Id = '0' and @customer_Id = '0'  )
	begin

		set @CsolCursor = CURSOR for
		select P.Part_Number, C.C_Name, CST.CST_Ship_Date, (COOL.COOL_Unit_Cost*CSLIT.CSLIT_Qty_Shipped) as Amount, CSLIT_Status, COOL_Requested_Date, SO.SO_Sales_Person, 
                CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER		
		from Customer_Open_Order_LineItem COOL, 
		     customer_shipping_line_item_table CSLIT,
		     Product P, Sales_Order SO, Customer C , Customer_Shipping_Table CST

		where CST.CST_Ship_Date>=@date_From
		and CST.CST_Ship_Date<=@date_To
		and CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID
		and CSLIT.LineItem_ID = COOL.LineItem_ID
		and COOL.Product_ID = P.Product_ID
		and COOL.SalesOrder_ID = SO.SalesOrder_ID
		and SO.SO_Is_Sample = 1
		and SO.Customer_ID = C.Customer_ID 
		and CST.CST_STATUS = 'SHIPPED'
		and so_is_consignment = 1
              
		if (@category = 'REQUESTED')                
        	begin
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER		
			from product,
	                     Customer_Open_Order_LineItem COOL,
			     Sales_Order,Customer 

			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID
			and Sales_Order.SO_Is_Sample = 1 
			and Sales_Order.Customer_ID=customer.Customer_ID
	                and COOL_Status <> 'SHIPPED'
	                and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
		end
		else if (@category = 'SCHEDULE')
		begin
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        --Sales_Order.SO_EndUser,
			CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER,		
                        COOL.COOL_Requested_Date
			from product,
	                     Customer_Open_Order_LineItem COOL,
			     Sales_Order,Customer 

			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
			and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	                
		end
	                
	END
	if (@product_Id <> '0' and @customer_Id = '0'  )
	begin
		set @CsolCursor = CURSOR for
 		select P.Part_Number, C.C_Name, CST.CST_Ship_Date, (COOL.COOL_Unit_Cost*CSLIT.CSLIT_Qty_Shipped) as Amount, CSLIT_Status, COOL_Requested_Date, SO.SO_Sales_Person, 
                CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER		
		from Customer_Open_Order_LineItem COOL, 
		     customer_shipping_line_item_table CSLIT,
		     Product P, Sales_Order SO, Customer C , Customer_Shipping_Table CST

		where CST.CST_Ship_Date>=@date_From
		and CST.CST_Ship_Date<=@date_To
		and CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID
		and CSLIT.LineItem_ID = COOL.LineItem_ID
		and COOL.Product_ID = @product_Id
                and COOL.Product_ID = P.Product_ID
		and COOL.SalesOrder_ID = SO.SalesOrder_ID
		and SO.SO_Is_Sample = 1
		and SO.Customer_ID = C.Customer_ID 
		and CST.CST_STATUS = 'SHIPPED'
		and so_is_consignment = 1

		if (@category = 'REQUESTED')                
        	begin
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER		
			from product,
				Customer_Open_Order_LineItem COOL,
				Sales_Order,Customer 
			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.SO_Is_Sample = 1
			and Sales_Order.Customer_ID=customer.Customer_ID
	                and COOL_Status <> 'SHIPPED'
	                and COOL.Product_ID= @product_Id
			and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
		end
		else if (@category = 'SCHEDULE')
		begin
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER,		
                        COOL.COOL_Requested_Date
			from product,
				Customer_Open_Order_LineItem COOL,
				Sales_Order,Customer 
			where 
			
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.Customer_ID=customer.Customer_ID
			and Sales_Order.SO_Is_Sample = 1
	                and COOL_Status <> 'SHIPPED'
			and COOL.Product_ID= @product_Id
			and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	                
		end

	end
	if (@product_Id = '0' and @customer_Id <> '0'  )
	begin
		set @CsolCursor = CURSOR for
		select P.Part_Number, C.C_Name, CST.CST_Ship_Date, (COOL.COOL_Unit_Cost*CSLIT.CSLIT_Qty_Shipped) as Amount, CSLIT_Status, COOL_Requested_Date, SO.SO_Sales_Person, 
                CASE WHEN ( SO.SO_ODM_ID IS NULL OR SO.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS END_USER		

		from Customer_Open_Order_LineItem COOL, 
		     customer_shipping_line_item_table CSLIT,
		     Product P, Sales_Order SO, Customer C , Customer_Shipping_Table CST

		where CST.CST_Ship_Date>=@date_From
		and CST.CST_Ship_Date<=@date_To
		and CST.CustomerShipping_ID = CSLIT.CustomerShipping_ID
		and CSLIT.LineItem_ID = COOL.LineItem_ID
		and COOL.Product_ID = P.Product_ID
		and COOL.SalesOrder_ID = SO.SalesOrder_ID
		and SO.SO_Is_Sample = 1
		and SO.Customer_ID = @customer_Id
                and SO.Customer_ID = C.Customer_ID 
		and CST.CST_STATUS = 'SHIPPED'
		and so_is_consignment = 1 

		If (@category = 'REQUESTED')                
        	begin
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Requested_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER		
			from product,
				Customer_Open_Order_LineItem COOL,
				Sales_Order,Customer 
			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID
			and Sales_Order.SO_Is_Sample = 1
			and Sales_Order.Customer_ID=customer.Customer_ID
	                and COOL_Status <> 'SHIPPED'
	                and customer.Customer_ID= @customer_Id 
			and ((COOL.COOL_Requested_Date>=@date_From and COOL.COOL_Requested_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
		end
		else if (@category = 'SCHEDULE')
		begin
			set @CoolCursor = CURSOR for
			select product.Part_Number,
	        	customer.C_Name,COOL.COOL_Schedule_Date,COOL.COOL_Amount as Amount, COOL.COOL_Status , LineItem_ID , COOL_Unit_Cost, COOL_Revised_Date, Sales_Order.SO_Sales_Person, 
                        CASE WHEN ( SALES_ORDER.SO_ODM_ID IS NULL OR SALES_ORDER.SO_ODM_ID = -1 ) THEN ''
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SALES_ORDER.SO_ODM_ID )
			END AS END_USER,		
                        COOL.COOL_Requested_Date
			from product,
				Customer_Open_Order_LineItem COOL,
				Sales_Order,Customer 
			where 
			COOL.Product_ID=product.Product_ID 
			and COOL.SalesOrder_ID=Sales_Order.SalesOrder_ID 
			and Sales_Order.SO_Is_Sample = 1
			and Sales_Order.Customer_ID=customer.Customer_ID
	                and COOL_Status <> 'SHIPPED'
			and customer.Customer_ID= @customer_Id 
			and ((COOL.COOL_Schedule_Date>=@date_From and COOL.COOL_Schedule_Date<=@date_To)
			or (cool.cool_status = 'approved'  or cool.cool_status = 'hold'))
	                
			
		end
	end

	open @CsolCursor 
		fetch next from @CsolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user
		while @@fetch_status = 0
		begin
			if(@amount is null)
			begin
				set @amount = 0
			end

			if(@schedule_Date is null OR @schedule_Date= '')
			begin
				-- Prabhanjan(2 Jun 04)if schedule date is null set schedule date as requested date so that the item falls in that quarter.
				--set @schedule_Date = CONVERT(datetime,'01/01/'+@year)
				set @schedule_Date = @requested_date
			end

			insert into @results(PartNumber,CustomerName,ScheduleDate,Amount,Status,Type, SalesRep, EndUser) values(@part_Number,@customer_Name,@schedule_Date, @amount, @status, 'COOL', @sales_rep, @end_user) 	
			set @part_number = null
			set @customer_name = null
			set @schedule_date = null
			set @amount = null
			set @status = null
			set @requested_date = null

			fetch next from @CsolCursor into @part_Number, @customer_Name,@schedule_Date,@amount,@status, @requested_date, @sales_rep, @end_user
		end
	close @CsolCursor
	deallocate @CsolCursor


	open @CoolCursor 
		-- Modified by Gandhi on 1st Dec 2004
		if (@category = 'REQUESTED')                
        	begin
		fetch next from @CoolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user
		end
		else if (@category = 'SCHEDULE')
		begin
		fetch next from @CoolCursor
		into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date
		end	
		-- Ends here - Gandhi
		while @@fetch_status = 0
		begin
					
			if(@amount is null)
			begin
				set @amount = 0
			end
			
			if (@category = 'REQUESTED')                
        		begin
				
				if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
				begin
					set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
				end
				else
				begin
					set @schedule_Date = @schedule_Date
				end
			end	
	
			if (@category = 'SCHEDULE')
			begin
				if(@schedule_Date is null OR @schedule_Date= '')
				begin
					
					set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
	
					-- Added by Gandhi on 30th Oct 2004 to display quarters as strictly start from requested date when schedule date is null and less than requested date.
					/*if(@schedule_Date >= @req_date)
					begin
						set @schedule_Date = @schedule_Date
					end
					else
					begin
						set @schedule_Date = @req_date
					end*/
					-- Ends here - Gandhi
				end
				else
				begin
					set @schedule_Date = @schedule_Date

					if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
					begin
						set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end
				end
			end
			-- Modified by Prabhanjan (07 Jul 2004)- IF condition added.If category is schedule, then check for revised date and if present take revised date to calculate quarter.
			if (@category = 'SCHEDULE')
			begin
				if(@revised_date is not null OR @revised_date <> '')
				begin
					set @schedule_Date = @revised_date
					
					if( @schedule_Date < CONVERT(datetime,CURRENT_TIMESTAMP) )
					begin
						set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
					end
					else
					begin
						set @schedule_Date = @schedule_Date
					end

					
				end
			end

			select @Shipped_Qty = isNull(sum(CSLIT_Qty_Shipped),0) from Customer_Shipping_Line_Item_Table where LineItem_ID = @LineItem_ID and CSLIT_Status = 'SHIPPED'
			
			set @amount = @amount - (@Unit_Price * @Shipped_Qty)	

			if ( @amount < 0 )
			begin
				set @amount = 0	
			end

			insert into @results(PartNumber,CustomerName,ScheduleDate,Amount,Status,Type, SalesRep, EndUser) values(@part_Number,@customer_Name,@schedule_Date, @amount ,@status, 'COOL', @sales_rep, @end_user ) 	

			set @part_number = null
			set @customer_name = null
			set @schedule_date = null
			set @amount = null
			set @status = null
			set @LineItem_ID = null
			set @unit_price = null
			set @requested_date = null
			set @revised_date = null
			-- Modified by Gandhi on 1st Dec 2004
			if (@category = 'REQUESTED')                
	        	begin
			fetch next from @CoolCursor into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user
			end
			else if (@category = 'SCHEDULE')                
	        	begin
			fetch next from @CoolCursor into @part_Number, @customer_Name,@schedule_Date,@amount, @status , @LineItem_ID , @Unit_Price, @revised_date, @sales_rep, @end_user, @req_date
			end
			-- Ends here - Gandhi
--print 'schdate:'+convert(varchar,@schedule_Date)	
	end
	close @CoolCursor
	deallocate @CoolCursor

	declare @lineItemStatus varchar(15)
	declare @lineItemCloseDate datetime

	declare @RmaItemCursor cursor

	if (@product_Id <> '0' and @customer_Id <> '0'  )
	begin
		set @RmaItemCursor = cursor for
		select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date,
		CASE WHEN ( RT.END_USER_ID IS NULL OR RT.END_USER_ID = '' OR RT.END_USER_ID = -1 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = RT.END_USER_ID )
		END AS END_USER
		from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
		where 	
			RT.RMA_ID = RLIT.RMA_ID 
			and RMA_Status <> 'CANCELLED'
			and PRO.Part_Number = RLIT.Part_Number
			and PRO.ProcessStep_ID = PS.ProcessStep_ID
			and PS.PS_Type_Name = 'Finished Goods'
			and PRO.Product_ID = @product_Id
			and RT.Customer_Name = CUST.C_Name
			and CUST.Customer_ID = @customer_Id
	end
	else if (@product_Id <> '0' and @customer_Id = '0'  )
	begin	

		set @RmaItemCursor = cursor for
		select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date,
		CASE WHEN ( RT.END_USER_ID IS NULL OR RT.END_USER_ID = '' OR RT.END_USER_ID = -1 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = RT.END_USER_ID )
		END AS END_USER
		from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
		where 	
			RT.RMA_ID = RLIT.RMA_ID 
			and RMA_Status <> 'CANCELLED'
			and RT.Customer_Name = CUST.C_Name 
			and RLIT.Part_Number = PRO.Part_Number
			and PRO.ProcessStep_ID = PS.ProcessStep_ID
			and PS.PS_Type_Name = 'Finished Goods'
			and PRO.Product_ID = @product_Id
	end		
	else if (@product_Id = '0' and @customer_Id <> '0'  )
	begin
		set @RmaItemCursor = cursor for
		select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date,
		CASE WHEN ( RT.END_USER_ID IS NULL OR RT.END_USER_ID = '' OR RT.END_USER_ID = -1 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = RT.END_USER_ID )
		END AS END_USER
		from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
		where 	
			RT.RMA_ID = RLIT.RMA_ID 
			and RMA_Status <> 'CANCELLED'
			and RLIT.Part_Number = PRO.Part_Number
			and PRO.ProcessStep_ID = PS.ProcessStep_ID
			and PS.PS_Type_Name = 'Finished Goods'
			and RT.Customer_Name = CUST.C_Name
			and CUST.Customer_ID = @customer_Id
	end		
	else if (@product_Id = '0' and @customer_Id = '0'  )
	begin
		set @RmaItemCursor = cursor for
		select Customer_Name , RLIT.Part_Number , RMA_Status , RMA_Requested_Date , RT.Close_Date , Quantity , Unit_Price, Is_Credit , RLIT.status , RLIT.Close_Date,
		CASE WHEN ( RT.END_USER_ID IS NULL OR RT.END_USER_ID = '' OR RT.END_USER_ID = -1 ) THEN ''
		ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = RT.END_USER_ID )
		END AS END_USER
		from RMA_Table RT, RMA_Line_Item_Table RLIT, Customer CUST, Product PRO, Process_Step PS
		where 	
			RT.RMA_ID = RLIT.RMA_ID 
			and RMA_Status <> 'CANCELLED'
			and RT.Customer_Name = CUST.C_Name 
			and RLIT.Part_Number = PRO.Part_Number
			and PRO.ProcessStep_ID = PS.ProcessStep_ID
			and PS.PS_Type_Name = 'Finished Goods'
	end
		
	declare @qty int
	declare @RMA_Requested_Date datetime
	declare @RMA_Close_Date     datetime
	
	-- Prabhanjan (27 May 2004)
	declare @Is_Credit int
	SET @amount = 0
	SET @Is_Credit = 1

	declare @RmaEndUser varchar(50) --Added by Gandhi on 17th March 2005	

	open @RmaItemCursor
	fetch next from @RmaItemCursor into @customer_Name , @part_Number , @status , @RMA_Requested_Date, @RMA_Close_Date , @qty , @Unit_Price, @Is_Credit , @lineItemStatus , @lineItemCloseDate, @RmaEndUser
	while @@fetch_status = 0
	begin
		if ( @lineItemStatus = 'CLOSE' )
		begin
			set @status = 'CLOSE'
			set @RMA_Close_Date = @lineItemCloseDate
		end


		if (@status = 'ISSUED')
		begin
			-- Prabhanjan(2 Jun 04) if RMA is in ISSUED state then set schedule date as current date so that the item falls in the current quarter.
			-- set @schedule_Date = @RMA_Requested_Date
			set @schedule_Date = CONVERT(datetime,CURRENT_TIMESTAMP)
			set @status = 'APPROVED'
		end
		else if (@status = 'CLOSE')
		begin
			set @schedule_Date = @RMA_Close_Date
			set @status = 'SHIPPED'
		end

		-- Added by Prabhanjan (27 May 2004). If Credit is selected, then only we need to show the amount. Otherwise, amount should be zero. 
		-- (Is_Credit = 0 ==> Credit Selected & Is_Credit = 1 ==> Credit Not Selected)
		IF( @Is_Credit = 1)
		BEGIN
			SET @amount = 0
		END
		ELSE IF (@Is_Credit = 0)
		BEGIN
			SET @amount = @qty * @unit_price 
		END
		

		insert into @results(PartNumber,CustomerName,ScheduleDate,Amount,Status,Type, SalesRep, EndUser) values(@part_Number,@customer_Name,@schedule_Date, @amount,@status,'RMA', '', @RmaEndUser ) 		
		
		SET @amount = 0
		SET @Is_Credit = 1
		
		set @customer_Name = null
		set @part_Number = null
		set @status = null
		set @RMA_Requested_Date = null
		set @RMA_Close_Date = null
		set @qty = null
		set @Unit_Price = null
		set @Is_Credit = null

		fetch next from @RmaItemCursor into @customer_Name , @part_Number , @status , @RMA_Requested_Date, @RMA_Close_Date , @qty , @Unit_Price, @Is_Credit , @lineItemStatus , @lineItemCloseDate, @RmaEndUser
	end	
		
	close @RmaItemCursor
	deallocate @RmaItemCursor

	-- Added by Prabhanjan on 24-Jul-2004
	if(@salesRep = '0' and @endUser = '0')
	begin
		select * from @results order by customername
	end
	else if(@salesRep = '0' and @endUser <> '0')
	begin
		select * from @results where EndUser = @endUser 
	end
	else if(@salesRep <> '0' and @endUser = '0')
	begin
		select * from @results where SalesRep = @salesRep
	end
	else if(@salesRep <> '0' and @endUser <> '0')

	begin
		select * from @results where SalesRep = @salesRep and EndUser = @endUser
	end
	else
	begin
		select * from @results
	end	
	


